﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using Weight.Base;
using Weight.Model;
using Dapper.Contrib.Extensions;

namespace Weight.Dal.Image
{
    public class ImageDal
    {
        /// <summary>
        /// 创建图片数据库库
        /// </summary>
        /// <param name="path"></param>
        /// <param name="dataBaseName"></param>
        /// <param name="logDataBaseName"></param>
        /// <returns></returns>
        public bool  CreateImageData(string path,string dbName)
        {
            string dataBaseName = dbName + "_data";
            string logDataBaseName = dbName + "_log";
            string dataPath = path + "\\" + dataBaseName + ".mdf";
            string logDataPath = path + "\\" + logDataBaseName + ".ldf";
            StringBuilder sqlBuider = new StringBuilder(" create database ");
            sqlBuider.Append(dbName).Append(" on primary")
                .Append("(")
                .Append("name='").Append(dataBaseName).Append("',")
                .Append(@" filename='").Append(dataPath).Append("',")
                .Append("size=10MB,")
                .Append("filegrowth=10%")
                .Append(")")
                .Append("log on")
                .Append("(")
                .Append("name='").Append(logDataBaseName).Append("',")
                .Append(@"filename='").Append(logDataPath).Append("',")
                .Append("size=5MB,")
                .Append("filegrowth=10%")
                .Append(")");
           
           using(IDbConnection conn =SessionFactory.CreateConnection())
            {
                conn.Execute(sqlBuider.ToString());
                return true;
            }
        }

        /// <summary>
        /// 判断图数据库是否已经存在
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        /// <returns></returns>
        public bool DbIsExist(string dbName)
        {
            string sql = "select count(1) from sysdatabases where name=@dbName";
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                return  conn.ExecuteScalar<int>(sql,new { dbName }) >  0;
            }
        }

        /// <summary>
        /// 获取业务数据库的路径
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        /// <returns></returns>
        public string GetDataBasePath(string dbName)
        {
            string sql = "select fileName from master.dbo.sysdatabases where name=@dbName";
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
               return  conn.Query<string>(sql, new { dbName }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取数据库当前年份
        /// </summary>
        /// <returns></returns>
        public string GetYearFromDb()
        {
            string sql = " select Year(getdate()) ";
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                return conn.Query<string>(sql).FirstOrDefault();
            }
        }

        /// <summary>
        /// 称重图标是否存在，不存在则创建
        /// </summary>
        /// <returns></returns>
        public bool ImageTableIsExist(string year)
        {
            string sql = "select count(1) from sysObjects where Id=OBJECT_ID(N'WeightImage') and xtype='U'";
            using(IDbConnection conn = SessionFactory.CreateImageConnection(year))
            {
               return conn.ExecuteScalar<int>(sql) > 0;
            }
        }

        /// <summary>
        /// 创建图标
        /// </summary>
        /// <returns></returns>
        public bool CreateWeightImgTable(string year)
        {
            string sql = @"
                Create table WeightImage
                (ProNum nvarchar(50),
                TareImg1 image,
                TareImg2 image,
                TareImg3 image,
                TareImg4 image,
                TareImg5 image,
                TareImg6 image,
                GrossImg1 image,
                GrossImg2 image,
                GrossImg3 image,
                GrossImg4 image,
                GrossImg5 image,
                GrossImg6 image,
                IsDelete  bit,
                CreateTime nvarchar(50),
                CreateBy nvarchar(50),
                UpdateFlag int,
                UploadFlag int
                )"; 
            using(IDbConnection conn = SessionFactory.CreateImageConnection(year))
            {
                conn.Execute(sql);
                return true;
            }
        }

        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public bool InsertImage(WeightImageEntity entity,string year)
        {
            using(IDbConnection conn  = SessionFactory.CreateImageConnection(year))
            {
                conn.Insert(entity);
                return true;
            }
        }

        /// <summary>
        ///获取图片
        /// </summary>
        /// <param name="pronum"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public WeightImageEntity Get(string pronum, string year)
        {
            using (IDbConnection conn = SessionFactory.CreateImageConnection(year))
            {
                string sql = "select * from  WeightImage where ProNum=@pronum";
               return  conn.Query<WeightImageEntity>(sql,new { pronum }).FirstOrDefault();
            }
        }


    }
}
